import openpyxl


def get_sheets(filename):
    if not isinstance(filename, str):
        print('get_sheets()参数错误，期望filename为str类型,实际类型为:' + type(filename))
        exit(0)
    try:
        wb = openpyxl.load_workbook(filename=filename)
        sheets = wb.get_sheet_names()
    except Exception:
        print('get_sheets()打开文件失败:' + filename)
        exit(0)
    else:
        wb.close()
        return sheets


def get_sheet_headers(filename, sheet_name, head_row_index=1):
    if not isinstance(filename, str) or not isinstance(sheet_name, str):
        print('get_table_headers()参数错误，期望filename&sheet_name为str类型,实际类型为:' + type(filename) + '&' + type(sheet_name))
        exit(0)

    ans = []
    try:
        # 首先获取到文件中的sheet_name页
        wb = openpyxl.load_workbook(filename=filename)
        if sheet_name not in wb.get_sheet_names():
            raise Exception('get_table_headers() ' + filename + '没有sheet页' + sheet_name)
        sheet = wb.get_sheet_by_name(sheet_name)
        wb.close()
        # 然后读出sheet_name页中的表头
        if head_row_index < sheet.min_column or head_row_index > sheet.max_column:
            raise Exception('get_table_headers()参数head_row_index超出范围, head_row_index = ' + str(head_row_index))
        for cell in sheet[head_row_index]:
            ans.append(cell.value)
    except Exception as err:
        print(err)
        exit(0)

    return ans


def search(filename, sheet_name, head_row_index=1, filter_list=None, edit_list=None):
    # 0 参数检查
    if not isinstance(filename, str) or not isinstance(sheet_name, str):
        print('search() 参数错误')
        exit(0)
    if filter_list is None:
        filter_list = []
    if edit_list is None:
        edit_list = []

    # 1 得到sheet_name页的全部内容
    data = []
    try:
        # 1.1 首先获取到文件中的sheet_name页
        wb = openpyxl.load_workbook(filename=filename)
        if sheet_name not in wb.get_sheet_names():
            raise Exception('get_table_headers() ' + filename + '没有sheet页' + sheet_name)
        sheet = wb.get_sheet_by_name(sheet_name)
        # 1.2 然后读出sheet_name页的全部内容, 每一行作为一个map，表头为k，对应的值为val
        for i in range(head_row_index + 1, sheet.max_row + 1):
            line = {}
            for j in range(sheet.min_column, sheet.max_column + 1):
                if sheet.cell(row=i, column=j).value is None:
                    line[sheet.cell(row=head_row_index, column=j).value] = ''
                else:
                    line[sheet.cell(row=head_row_index, column=j).value] = sheet.cell(row=i, column=j).value
            data.append(line)
        wb.close()
    except Exception as err:
        print(err)
        exit(0)

    # 2 使用过滤函数列表过滤
    for func in filter_list:
        if not isinstance(func, type(search)):
            continue
        tmp = []
        for line in data:
            if func(line):
                tmp.append(line)
        data = tmp

    # 3 使用编辑函数列表编辑
    for func in edit_list:
        if not isinstance(func, type(search)):
            continue
        tmp = []
        for line in data:
            tmp.append(func(line))
        data = tmp

    return data


def edit(filename, sheet_name, data=None, edit_list=None, head_row_index=1):
    # 0 参数检查
    if not isinstance(filename, str) or not isinstance(sheet_name, str):
        print('update_by_key() 参数错误，filename和sheet_name必须为str类型')
        exit(0)

    # 1 打开sheet页并遍历每一行进行修改
    try:
        # 1.1 首先获取到文件中的sheet_name页
        wb = openpyxl.load_workbook(filename=filename)
        if sheet_name not in wb.get_sheet_names():
            raise Exception('get_table_headers() ' + filename + '没有sheet页' + sheet_name)
        sheet = wb.get_sheet_by_name(sheet_name)
        # 1.2 然后读出sheet_name页的内容，逐行修改
        for row in range(head_row_index + 1, sheet.max_row + 1):
            line = {}
            for col in range(sheet.min_column, sheet.max_column + 1):
                if sheet.cell(row=row, column=col).value is None:
                    line[sheet.cell(row=head_row_index, column=col).value] = ''
                else:
                    line[sheet.cell(row=head_row_index, column=col).value] = sheet.cell(row=row, column=col).value
            for func in edit_list:
                if not isinstance(func, type(edit)):
                    continue
                line = func(line, data)
            for col in range(sheet.min_column, sheet.max_column + 1):
                if sheet.cell(row=head_row_index, column=col).value in line.keys():
                    sheet.cell(row=row, column=col).value = line[sheet.cell(row=head_row_index, column=col).value]
                else:
                    sheet.cell(row=row, column=col).value = ''
        wb.save(filename)
        wb.close()
    except Exception as err:
        print(err)
        exit(0)


def insert_lines(filename, sheet_name, data, mode, head_row_index=1, start_row_index=2):
    # 0 检查参数
    if not isinstance(filename, str) or not isinstance(sheet_name, str):
        print('insert() 参数类型错误')
        exit(0)
    if data is None or not isinstance(data, list):
        print('insert() 参数错误，data非空并类型为list')
        exit(0)
    mode_list = ['overwrite', 'insert']
    if mode not in mode_list:
        print('insert() 仅支持overwrite和insert模式')
        exit(0)
    # 1 打开sheet_页并从内容的第一行开始写入data的数据
    try:
        # 1.1 首先获取到文件中的sheet_name页
        wb = openpyxl.load_workbook(filename=filename)
        if sheet_name not in wb.get_sheet_names():
            raise Exception('get_table_headers() ' + filename + '没有sheet页' + sheet_name)
        sheet = wb.get_sheet_by_name(sheet_name)
        # 1.2 如果是insert模式，先把start_row_index之后的内容读取出来，插入到data的数据后，之后把data全部写入表格
        for row in range(start_row_index, sheet.max_row + 1):
            line = {}
            for col in range(sheet.min_column, sheet.max_column + 1):
                line[sheet.cell(row=head_row_index, column=col).value] = sheet.cell(row=row, column=col).value
                sheet.cell(row=row, column=col).value = ''
            if mode == 'insert':
                data.append(line)
        for i in range(len(data)):
            line = data[i]
            row = start_row_index + i
            for col in range(sheet.min_column, sheet.max_column + 1):
                if sheet.cell(row=head_row_index, column=col).value in line.keys():
                    sheet.cell(row=row, column=col).value = line[sheet.cell(row=head_row_index, column=col).value]
        wb.save(filename)
        wb.close()
    except Exception as err:
        print(err)
        exit(0)
